"""
审核数据访问对象
"""
import json
from typing import List, Optional, Dict, Any
from datetime import datetime, date
from sqlalchemy.orm import Session
from sqlalchemy import func, desc
from database.connection import get_db
from database.models import ContentAuditRecord, AuditStatistics
from models.schemas import AuditResponse, AuditRecord
from utils.logger import audit_logger


class AuditDAO:
    """审核数据访问对象"""
    
    def save_audit_record(self, audit_response: AuditResponse) -> bool:
        """保存审核记录"""
        try:
            db = next(get_db())
            
            # 创建审核记录
            record = ContentAuditRecord(
                post_id=audit_response.post_id,
                audit_result=audit_response.audit_result.value,
                confidence=audit_response.confidence,
                reasons=audit_response.reasons,
                details=audit_response.details,
                sensitive_words=audit_response.details.get('sensitive_words'),
                text_classification=audit_response.details.get('text_classification'),
                sentiment_analysis=audit_response.details.get('sentiment_analysis'),
                image_audit=audit_response.details.get('image_audit')
            )
            
            db.add(record)
            db.commit()
            
            audit_logger.info(f"审核记录已保存: 帖子ID {audit_response.post_id}")
            return True
            
        except Exception as e:
            audit_logger.error(f"保存审核记录失败: {e}")
            if 'db' in locals():
                db.rollback()
            return False
        finally:
            if 'db' in locals():
                db.close()
    
    def get_audit_record(self, post_id: int) -> Optional[AuditRecord]:
        """获取审核记录"""
        try:
            db = next(get_db())
            
            record = db.query(ContentAuditRecord).filter(
                ContentAuditRecord.post_id == post_id
            ).order_by(desc(ContentAuditRecord.created_at)).first()
            
            if record:
                return AuditRecord(
                    id=record.id,
                    post_id=record.post_id,
                    audit_result=record.audit_result,
                    confidence=record.confidence,
                    reasons=json.dumps(record.reasons) if record.reasons else None,
                    details=json.dumps(record.details) if record.details else None,
                    created_at=record.created_at,
                    updated_at=record.updated_at
                )
            
            return None
            
        except Exception as e:
            audit_logger.error(f"获取审核记录失败: {e}")
            return None
        finally:
            if 'db' in locals():
                db.close()
    
    def get_audit_records_by_result(self, audit_result: str, limit: int = 100) -> List[AuditRecord]:
        """根据审核结果获取记录"""
        try:
            db = next(get_db())
            
            records = db.query(ContentAuditRecord).filter(
                ContentAuditRecord.audit_result == audit_result
            ).order_by(desc(ContentAuditRecord.created_at)).limit(limit).all()
            
            result = []
            for record in records:
                result.append(AuditRecord(
                    id=record.id,
                    post_id=record.post_id,
                    audit_result=record.audit_result,
                    confidence=record.confidence,
                    reasons=json.dumps(record.reasons) if record.reasons else None,
                    details=json.dumps(record.details) if record.details else None,
                    created_at=record.created_at,
                    updated_at=record.updated_at
                ))
            
            return result
            
        except Exception as e:
            audit_logger.error(f"获取审核记录失败: {e}")
            return []
        finally:
            if 'db' in locals():
                db.close()
    
    def update_daily_statistics(self, audit_response: AuditResponse):
        """更新每日统计"""
        try:
            db = next(get_db())
            today = date.today().strftime('%Y-%m-%d')
            
            # 查找或创建今日统计记录
            stats = db.query(AuditStatistics).filter(
                AuditStatistics.date == today
            ).first()
            
            if not stats:
                stats = AuditStatistics(date=today)
                db.add(stats)
            
            # 更新统计数据，确保字段不为None
            stats.total_audits = (stats.total_audits or 0) + 1

            if audit_response.audit_result.value == 'PASS':
                stats.pass_count = (stats.pass_count or 0) + 1
            elif audit_response.audit_result.value == 'REJECT':
                stats.reject_count = (stats.reject_count or 0) + 1
            elif audit_response.audit_result.value == 'MANUAL':
                stats.manual_count = (stats.manual_count or 0) + 1
            
            # 更新违规类型统计
            self._update_violation_stats(stats, audit_response)
            
            # 更新平均置信度
            current_avg = stats.avg_confidence or 0.0
            total_confidence = (current_avg * (stats.total_audits - 1) + audit_response.confidence)
            stats.avg_confidence = total_confidence / stats.total_audits
            
            db.commit()
            
        except Exception as e:
            audit_logger.error(f"更新每日统计失败: {e}")
            if 'db' in locals():
                db.rollback()
        finally:
            if 'db' in locals():
                db.close()
    
    def _update_violation_stats(self, stats: AuditStatistics, audit_response: AuditResponse):
        """更新违规类型统计"""
        details = audit_response.details
        
        # 敏感词违规
        sensitive_words = details.get('sensitive_words', {})
        if sensitive_words.get('has_sensitive', False):
            stats.sensitive_words_count = (stats.sensitive_words_count or 0) + 1

        # 文本分类违规
        text_classification = details.get('text_classification', {})
        if not text_classification.get('is_normal', True):
            category = text_classification.get('category', '')
            if category == 'spam':
                stats.spam_count = (stats.spam_count or 0) + 1
            elif category == 'advertisement':
                stats.advertisement_count = (stats.advertisement_count or 0) + 1
            elif category == 'inappropriate':
                stats.inappropriate_count = (stats.inappropriate_count or 0) + 1

        # 图片违规
        image_audit = details.get('image_audit', [])
        for image_result in image_audit:
            if not image_result.get('is_safe', True):
                stats.image_violation_count = (stats.image_violation_count or 0) + 1
                break  # 只要有一张图片违规就计数一次
    
    def get_statistics_by_date_range(self, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        """获取日期范围内的统计数据"""
        try:
            db = next(get_db())
            
            stats = db.query(AuditStatistics).filter(
                AuditStatistics.date >= start_date,
                AuditStatistics.date <= end_date
            ).order_by(AuditStatistics.date).all()
            
            result = []
            for stat in stats:
                result.append({
                    'date': stat.date,
                    'total_audits': stat.total_audits,
                    'pass_count': stat.pass_count,
                    'reject_count': stat.reject_count,
                    'manual_count': stat.manual_count,
                    'sensitive_words_count': stat.sensitive_words_count,
                    'spam_count': stat.spam_count,
                    'advertisement_count': stat.advertisement_count,
                    'inappropriate_count': stat.inappropriate_count,
                    'image_violation_count': stat.image_violation_count,
                    'avg_confidence': stat.avg_confidence,
                    'avg_processing_time': stat.avg_processing_time
                })
            
            return result
            
        except Exception as e:
            audit_logger.error(f"获取统计数据失败: {e}")
            return []
        finally:
            if 'db' in locals():
                db.close()


# 全局审核DAO实例
audit_dao = AuditDAO()
